import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Dakalo and Peter
 */
public class Database 
{
    static final String DRIVER = "org.sqlite.JDBC";
    private String DATABASE_URL = "jdbc:sqlite:infinitysolutions.db";
    private ResultSet result;
    private ResultSet result2 = null;
    private Connection connection;
    private Statement statement;
    int lastInsertedId;
    String databaseName;

    
    /**
     * The default constructor. This constructor will connect to a database named infinitysolutions with username: dakalo and password: dakalo
     */
    public Database()
    {
        databaseName = "infinitysolutions";
        result = null;
        result2 = null;
        connection = null;
        statement = null;
    }
    
    public void addClasses(Vector<String> className)
    {
        FileParser parse = new FileParser();
        for(int i = 0;i < className.size() ;i++)
        {
            parse.parseString(className.get(i));
        }
    }

    
    /**
     * The connect method. This will connect to the database with the parameters specified in the constructor.
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    void connect() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
    {
	Class.forName(DRIVER);
	connection = DriverManager.getConnection(DATABASE_URL+databaseName);
    }
    
    
    /**
     * The disconnect method. This will close the database connection.
     * @throws SQLException
     */
    void disconnect() throws SQLException
    {
    	connection.close();
    }   
    
     /**
     * The initializeDB method. populates the tables in the DB
     * @throws SQLException
     */
    void initializeDB() throws SQLException
    {
    	statement = connection.createStatement();
        boolean dropClass = statement.execute("DROP TABLE IF EXISTS class;");
        boolean dropextends_implement = statement.execute("DROP TABLE IF EXISTS extends_implement;");
        boolean dropField = statement.execute("DROP TABLE IF EXISTS field;");
        boolean dropMethod = statement.execute("DROP TABLE IF EXISTS method;");
        boolean dropImports = statement.execute("DROP TABLE IF EXISTS imports;");
        boolean createClass = statement.execute("CREATE TABLE IF NOT EXISTS class ( "
                                    + "ClassName varchar(100) NOT NULL,"
                                    + "AccMode varchar(20) NOT NULL,"
                                    + "LineNum int(11) NOT NULL,"
                                    + " PRIMARY KEY (ClassName)"
                                    + ");" );  
        String extends_implement = "CREATE TABLE IF NOT EXISTS extends_implement ("
                                    + "RelationID integer primary key,"
                                    + "SourceClass varchar(100) NOT NULL,"
                                    + "DestinationClass varchar(100) NOT NULL,"
                                    + "Label varchar(50) NOT NULL"
                                    + ");";
        statement = connection.createStatement();
        boolean createextends_implement = statement.execute(extends_implement);  
        String field = "CREATE TABLE IF NOT EXISTS field ("
                          +" FieldID integer primary key,"
                         + " FieldName varchar(100) NOT NULL,"
                         + " FieldType varchar(15) NOT NULL,"
                         + " AccMode varchar(20) NOT NULL,"
                         + " MethodID int(11) DEFAULT NULL,"
                         + " ClassName varchar(100)  NOT NULL,"
                         + " BooleanArray varchar(100) NOT NULL,"
                         + " VariableScope varchar(20) NOT NULL,"
                         + " LineNum int(11) NOT NULL"
                       + " )" ;
        statement = connection.createStatement();
        boolean createField = statement.execute(field); 
        String imports = "CREATE TABLE IF NOT EXISTS imports ("
                        + "  importID integer primary key,"
                        + "  className varchar(100) NOT NULL,"
                        + "  importName varchar(500) NOT NULL"
                        + ");";
       
        statement = connection.createStatement();
        boolean createImports = statement.execute(imports); 
        String method = "CREATE TABLE IF NOT EXISTS method ("
                        + "  MethodID integer primary key,"
                        + "  MethodName varchar(100) NOT NULL,"
                        + "  RetType varchar(100) NOT NULL,"
                        + "  AccMode varchar(20) NOT NULL,"
                        + "  ClassName varchar(100) NOT NULL,"
                        + "  MethodParameterList varchar(1000) NOT NULL,"
                        + "  LineNum int(11) NOT NULL"
                       + " );";
        statement = connection.createStatement();
        boolean createMethod = statement.execute(method);
    }
 
    /**
     * The insertField method,This will add field declaration to the database
     * @return an int
     * @param FieldName String. The Field's name
     * @param AccMode String. The Access Mode of the Field. Example private or public
     * @param FieldType String. The Type of the Field. Example int or boolean
     * @param MethodID String. The MethodID of where the Field is.null if field is global
     * @param ClassName String. The Class where the field is defined
     * @param BooleanArray String. true if its an array and false otherwise
     * @param VariableScope String. VariableScope eg global or parameter or methodVariable
     */    
    
    public int insertField(String FieldName,String AccMode,String FieldType,String MethodID,String ClassName,String BooleanArray,String VariableScope,int LineNum)
    {
        try
        {
            statement = connection.createStatement();
            int insertValue = statement.executeUpdate("INSERT INTO field( FieldName,FieldType,AccMode,MethodID,ClassName,BooleanArray,VariableScope,LineNum)"+
            "VALUES('"+FieldName +"','"+FieldType +"','"+AccMode +"','"+MethodID + "','"+ClassName +"','"+BooleanArray +"','"+VariableScope +"','"+LineNum +"');   ");
        }
        catch(SQLException sql)
        {
            System.out.println("SQLException in Field INSERT STATEMENT");
        }
        try 
        {
            result = statement.getGeneratedKeys();
            if (result != null) 
            {  
                if (result.next()) 
                {  
                       lastInsertedId=result.getInt(1);
                }
             }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lastInsertedId;
    }
    
    
    /**
     * The insertMethod method,This will add method to the database
     * @return an int
     * @param MethodName String. The Method's name     
     * @param RetType String. The Return Type of the Method. Example int or boolean
     * @param AccMode String. The Access Mode of the method. Example private or public
     * @param ClassName String. The Class where the method is defined
     * @param MethodParameterList String. String with the ID's of the fields defined in that method     
     * @param LineNum String. line number where the method starts
     */        
    public int insertMethod(String MethodName,String RetType,String AccMode,String ClassName,String MethodParameterList,int LineNum)
    {
        try
        {
            statement = connection.createStatement();
            int insertValue = statement.executeUpdate("INSERT INTO method( MethodName,RetType,AccMode,ClassName,MethodParameterList,LineNum)"+
            "VALUES('"+MethodName +"','"+RetType +"','"+AccMode +"','"+ClassName +"','"+MethodParameterList +"','"+LineNum +"');   ");  
        }
        catch(SQLException sql)
        {
            System.out.println("SQLException in METHOD INSERT STATEMENT");
        }
        try 
        {
            result = statement.getGeneratedKeys();
            if (result != null) 
            {  
                if (result.next()) 
                {  
                    lastInsertedId=result.getInt(1);
                }
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lastInsertedId;
    }
    
     /**
     * The insertClass method,Inserts the class in the database
     * @param className String. The class name
     * @param access String. The access mode of the class
     */    
    public void insertClass(String className,String access,int LineNum)
    {
        try
        {
            statement = connection.createStatement();
            int insertValue = statement.executeUpdate("INSERT INTO class(ClassName,AccMode,LineNum)"+
            "VALUES('"+className +"','"+ access +"','"+ LineNum +"');   ");
        }
        catch(SQLException sql)
        {
            System.out.println("SQLException in CLASS INSERT statement");
        }
    }
     
    /**
     * The insertExtends_Implements method,Inserts the Extends_Implements relation in the database
     * @param SourceClassName String. The Source class name that implements or extends
     * @param DestinationClassName String. The class being extened or implemented
     * @param label String. Identifies the relation eg. extened or implemented
     */     
    public void insertExtends_Implements(String SourceClassName,String DestinationClassName,String label)
    {       
        try
        {
            statement = connection.createStatement();
            int insertValue = statement.executeUpdate("INSERT INTO extends_implement( SourceClass,DestinationClass,label)"+
            "VALUES('"+SourceClassName +"','"+ DestinationClassName +"','"+label +"');   ");
        }
        catch(SQLException sql)
        {
            System.out.println("SQLException in Ext_Iplements insert statement");
        }
    }
    
     /**
     * The updatemethodFieldList method,Updates the parameter list
     * @param methodFieldList String. List of ID's
     * @param MethodID String. The The methodID to update
     */
    void updatemethodFieldList(String methodFieldList,int MethodID) 
    {
        String query = "UPDATE method SET MethodParameterList ='"+methodFieldList+"' WHERE MethodID='"+MethodID+"';";
        try
        {
            statement = connection.createStatement();
            int insertValue = statement.executeUpdate(query);
        }
        catch(SQLException sql)
        {
            System.out.println("SQLException in updatemethodFieldList select statement");
        }
    }

    /**
     * The insertImport method,Inserts the imports in the database
     * @param className String. The class to insert into
     * @param importName String. The import name
     */  
    void insertImport(String className, String importName) 
    {
        try
        {
            statement = connection.createStatement();
            int insertValue = statement.executeUpdate("INSERT INTO imports( className,importName)"+
            "VALUES('"+className +"','"+ importName +"');   ");
        }
        catch(SQLException sql)
        {
            System.out.println("SQLException in Import insert statement");
        }
    }
    
    /**
     * The searchParameter method,Returns an array of line numbers of parameters
     * @return a vector of integers
     * @param parameterName String. The parameter name to to search
     */
    public Vector<Integer> searchParameter(String parameterName)
    {
        Vector<Integer> lineNum = new Vector<Integer>();        
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT LineNum FROM  field WHERE FieldName ='" +parameterName+ "' AND VariableScope = 'parameter';" );
            while(result.next())
            {
                lineNum.add(result.getInt(1));
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lineNum;
    } 
    
    /**
     * The globalConflict method,Checks if the is a conflict on the new name and the global names
     * @return a boolean
     * @param newName String. The new name which the user entered
     */
    public boolean globalConflict(String newName)
    {
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT FieldName FROM  field WHERE FieldName ='" +newName+ "' AND VariableScope = 'global';" );
            while(result.next())
            {
                return true;
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }     
    
    
    /**
     * The globalVariables method,Gets the global variables of the class
     * @return a Vector of Strings
     * @param className String. The className to get the global variables of
     */
    public Vector<String> globalVariables(String className)
    {
        Vector<String> globalVar = new Vector<String>();
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT FieldName FROM  field WHERE ClassName ='" +className+ "' AND VariableScope = 'global';" );
            while(result.next())
            {
                globalVar.add(result.getString(1));
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return globalVar;
    }
      
    
     /**
     * The innerConflict method,Checks if the is a conflict on the new name and the variables in the method
     * @return a boolean
     * @param newName String. The new name which the user entered
     */
    public boolean innerConflict(String newName)
    {
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT FieldName FROM  field WHERE FieldName ='" +newName+ "' AND VariableScope != 'global';" );
            while(result.next())
            {
                return true;
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }      
    
    public Vector<String> getParamater(String className)
    {
        Vector<String> params = new Vector<String>();
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT FieldName FROM field WHERE ClassName  ='" +className+ "' AND VariableScope = 'parameter';" );
            while(result.next())
            {
                params.add(result.getString(1));
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return params;
    }
    
    public Vector<String> getMethod(String className)
    {
        Vector<String> methods = new Vector<String>();
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT MethodName FROM method WHERE ClassName  ='" +className+ "' AND AccMode <> 'constructor';" );
            while(result.next())
            {
                methods.add(result.getString(1));
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return methods;
    }
    
    public Vector<String> getMethodParamater(String method, String className)
    {
        Vector<String> params = new Vector<String>();
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT MethodID FROM method WHERE MethodName = '"+method+"' AND ClassName = '"+className+"';");
            while(result.next())
            {
                statement = connection.createStatement();
                result2 = statement.executeQuery("SELECT FieldName FROM field WHERE ClassName  ='" +className+ "' AND VariableScope = 'parameter' AND MethodID = '"+result.getString(1) +"';" );
                while(result2.next())
                {
                    params.add(result2.getString(1));
                }
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return params;
    }
    
    public int getMethodLine(String method, String className)
    {
        int line = -1;
        try 
        {
            statement = connection.createStatement();
            result = statement.executeQuery("SELECT LineNum FROM method WHERE MethodName = '"+method+"' AND ClassName = '"+className+"';");
            while(result.next())
            {
                line = result.getInt(1);
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return line;
    }
}